﻿USE [AspectDemo]
GO
/****** Object:  StoredProcedure [dbo].[AddANewCustomer]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddANewCustomer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddANewCustomer]
GO
/****** Object:  StoredProcedure [dbo].[AddANewProduct]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddANewProduct]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddANewProduct]
GO
/****** Object:  StoredProcedure [dbo].[AddANewSalesOrder]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddANewSalesOrder]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddANewSalesOrder]
GO
/****** Object:  StoredProcedure [dbo].[AddANewSalesOrderLine]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AddANewSalesOrderLine]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AddANewSalesOrderLine]
GO
/****** Object:  StoredProcedure [dbo].[DeleteCustomer]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteCustomer]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteCustomer]
GO
/****** Object:  StoredProcedure [dbo].[DeleteProduct]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteProduct]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteProduct]
GO
/****** Object:  StoredProcedure [dbo].[DeleteSalesOrder]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteSalesOrder]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteSalesOrder]
GO
/****** Object:  StoredProcedure [dbo].[DeleteSalesOrderLine]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DeleteSalesOrderLine]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DeleteSalesOrderLine]
GO
/****** Object:  StoredProcedure [dbo].[GetAllCustomers]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllCustomers]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAllCustomers]
GO
/****** Object:  StoredProcedure [dbo].[GetAllProducts]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllProducts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAllProducts]
GO
/****** Object:  StoredProcedure [dbo].[GetAllSalesOrderLines]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllSalesOrderLines]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAllSalesOrderLines]
GO
/****** Object:  StoredProcedure [dbo].[GetAllSalesOrders]    Script Date: 03/11/2009 17:09:12 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllSalesOrders]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAllSalesOrders]
GO
/****** Object:  StoredProcedure [dbo].[GetAllSalesOrderLineDTOs]    Script Date: 03/12/2009 13:46:21 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllSalesOrderLineDTOs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAllSalesOrderLineDTOs]
GO
/****** Object:  StoredProcedure [dbo].[GetAllSalesOrderDTOs]    Script Date: 03/17/2009 13:49:01 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetAllSalesOrderDTOs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetAllSalesOrderDTOs]
GO

/****** Object:  StoredProcedure [dbo].[AddANewCustomer]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[AddANewCustomer]
	@CustomerName [varchar](20)
AS

INSERT INTO Customer
	(CustomerName)
VALUES
	(@CustomerName)

SELECT CustomerID, CustomerName
FROM Customer
WHERE CustomerID = SCOPE_IDENTITY()

GO
/****** Object:  StoredProcedure [dbo].[AddANewProduct]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[AddANewProduct]
	@ProductName [varchar](20)
AS

INSERT INTO Product
	(ProductName)
VALUES
	(@ProductName)

SELECT ProductID, ProductName
FROM Product
WHERE ProductID = SCOPE_IDENTITY()
GO
/****** Object:  StoredProcedure [dbo].[AddANewSalesOrder]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[AddANewSalesOrder]
	@CustomerID [int],
	@OrderDate [datetime]
AS

INSERT INTO SalesOrder
	(CustomerID, OrderDate)
VALUES
	(@CustomerID, @OrderDate)

SELECT SalesOrderID, OrderDate, CustomerID, CreateDate
FROM SalesOrder
WHERE SalesOrderID = SCOPE_IDENTITY()
GO
/****** Object:  StoredProcedure [dbo].[AddANewSalesOrderLine]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[AddANewSalesOrderLine]
	@SalesOrderID [int],
	@ProductID [int],
	@Quantity [decimal](18, 9)
AS

INSERT INTO SalesOrderLine
	(SalesOrderID, ProductID, Quantity)
VALUES
	(@SalesOrderID, @ProductID, @Quantity)

SELECT [SalesOrderLineID]
      ,[SalesOrderID]
      ,[ProductID]
      ,[Quantity]
FROM [SalesOrderLine]
WHERE SalesOrderLineID = SCOPE_IDENTITY()
GO
/****** Object:  StoredProcedure [dbo].[DeleteCustomer]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[DeleteCustomer]
	@CustomerID [int]
AS

DELETE FROM Customer WHERE CustomerID = @CustomerID
GO
/****** Object:  StoredProcedure [dbo].[DeleteProduct]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[DeleteProduct]
	@ProductID [int]
AS

DELETE FROM Product
WHERE ProductID = @ProductID
GO
/****** Object:  StoredProcedure [dbo].[DeleteSalesOrder]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[DeleteSalesOrder]
	@SalesOrderID [int]
AS

DELETE FROM SalesOrder
WHERE SalesOrderID = @SalesOrderID
GO
/****** Object:  StoredProcedure [dbo].[DeleteSalesOrderLine]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[DeleteSalesOrderLine]
	@SalesOrderLineID [int]
AS

DELETE FROM SalesOrderLine
WHERE SalesOrderLineID = @SalesOrderLineID
GO
/****** Object:  StoredProcedure [dbo].[GetAllCustomers]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[GetAllCustomers]
AS

SELECT CustomerID, CustomerName
FROM Customer
ORDER BY CustomerName

GO
/****** Object:  StoredProcedure [dbo].[GetAllProducts]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[GetAllProducts]
AS

SELECT ProductID, ProductName
FROM Product
GO
/****** Object:  StoredProcedure [dbo].[GetAllSalesOrderLines]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[GetAllSalesOrderLines]
AS

SELECT [SalesOrderLineID]
      ,[SalesOrderID]
      ,[ProductID]
      ,[Quantity]
FROM [SalesOrderLine]
GO
/****** Object:  StoredProcedure [dbo].[GetAllSalesOrders]    Script Date: 03/11/2009 17:09:32 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[GetAllSalesOrders]
AS

SELECT

	s.SalesOrderID, s.CustomerID, s.OrderDate, s.CreateDate

FROM [SalesOrder] s
 
GO
/****** Object:  StoredProcedure [dbo].[GetAllSalesOrderLineDTOs]    Script Date: 03/12/2009 13:38:45 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[GetAllSalesOrderLineDTOs]
AS

SELECT
	
	sl.Quantity, sl.SalesOrderLineID, sl.SalesOrderID, sl.ProductID,
	c.CustomerName, s.CustomerID,
	s.OrderDate, s.CreateDate, p.ProductName

FROM SalesOrderLine sl
INNER JOIN SalesOrder s
	ON s.SalesOrderID = sl.SalesOrderID
INNER JOIN Customer c
	ON c.CustomerID = s.CustomerID
INNER JOIN Product p
	ON p.ProductID = sl.ProductID
	
GO

/****** Object:  StoredProcedure [dbo].[GetAllSalesOrderDTOs]    Script Date: 03/17/2009 13:48:56 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[GetAllSalesOrderDTOs]
AS

SELECT

	s.SalesOrderID, s.CustomerID, s.OrderDate, s.CreateDate,
	c.CustomerName

FROM [SalesOrder]s
INNER JOIN Customer c
	ON c.CustomerID = s.CustomerID